package dao;

import model.Product;
import org.apache.commons.dbutils.*;
import org.apache.commons.dbutils.handlers.*;
import utils.DataSourceUtils;

import java.sql.SQLException;
import java.util.*;

public class ProductDao {


    public List<Product> GetAllOnSaleProduct() throws SQLException {
        QueryRunner r=new QueryRunner(DataSourceUtils.getDataSource());
        String sql="select * from product where status=1";
        //        try {
        //            Connection conn = DataSourceUtils.getDataSource().getConnection();
        //            System.out.println("conn对象：" + conn);
        //            System.out.println("当前连接到的数据库是：" + conn.getCatalog());
        //        } catch (Exception e) {
        //            e.printStackTrace();
        //            System.out.println("获取数据库连接时发生异常！");
        //        }
        return r.query(sql,new BeanListHandler<Product>(Product.class));
    }
    public List<Product> GetProductByReceiverId(int userId) throws SQLException {
        QueryRunner r=new QueryRunner(DataSourceUtils.getDataSource());
        String sql="select * from product where receiver_id=?";
        return r.query(sql,new BeanListHandler<Product>(Product.class),userId);
    }
    //通过userid获取其发布的所有在售或下架商品信息
    public List<Product> GetProductByReceiverIdAndStatus(int userId, boolean status) throws SQLException {
        QueryRunner r=new QueryRunner(DataSourceUtils.getDataSource());
        String sql="select * from product where receiver_id=? and status=?";
        return r.query(sql,new BeanListHandler<Product>(Product.class),userId,status);
    }
    //通过userid获取其发布的所有下架商品信息
    public List<Product> GetNotOnSaleProductByReceiverId(int userId) throws SQLException {
        QueryRunner r=new QueryRunner(DataSourceUtils.getDataSource());
        String sql="select * from product where user_id=? and status=0";
        return r.query(sql,new BeanListHandler<Product>(Product.class),userId);
    }
    //通过类型id获取商品
    public List<Product> selectGoodsByCategoryID(int typeID) throws SQLException {
        if(typeID==0)
        {
            String sql="select * from product";
            QueryRunner r=new QueryRunner(DataSourceUtils.getDataSource());
            return  r.query(sql,new BeanListHandler<Product>(Product.class));
        }
        else
        {
            String sql="select * from product where category_id=?";
            QueryRunner r=new QueryRunner(DataSourceUtils.getDataSource());
            return  r.query(sql,new BeanListHandler<Product>(Product.class),typeID);
        }
    }
    //根据是否下架获取对应商品信息
    public List<Product> selectProductByStatus(boolean status) throws SQLException {

        String sql = "SELECT * FROM product WHERE status = ?";
        QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
        return runner.query(sql, new BeanListHandler<>(Product.class), status);
    }
    //根据商品id获取发布人id
    public int getReceiverIdById(int productId) throws SQLException {
        QueryRunner r=new QueryRunner(DataSourceUtils.getDataSource());
        String sql="select receiver_id from product where id=?";
        return r.query(sql, new ScalarHandler<Integer>(), productId);
    }
    //根据id获取商品
    public Product getGoodsById(int id) throws SQLException {
        QueryRunner r = new QueryRunner(DataSourceUtils.getDataSource());
        String sql = "select p.id,p.name,p.detail,p.cover,p.pickup_address,p.proxy_address,p.receiver_id,p.phone_number,p.price,p.status,c.id category_id,c.name categoryname, u.avatar useravatar, u.nickname usernickname from product p,category c,user u where p.id = ? and p.category_id=c.id and p.receiver_id=u.id";
        return r.query(sql, new BeanHandler<Product>(Product.class),id);
    }
    //根据商品id获取其价格
    public float getPriceById(int id) throws SQLException {
        QueryRunner r = new QueryRunner(DataSourceUtils.getDataSource());
        String sql = "select price from product where id=?";
        Number num = r.query(sql, new ScalarHandler<>(), id);
        return num == null ? 0f : num.floatValue();
    }

    public void insert(Product p)throws SQLException{
        QueryRunner r = new QueryRunner(DataSourceUtils.getDataSource());
        //创建实例r，传入数据源DataSource
        String sql = "insert into product(name, detail, cover, pickup_address, proxy_address, receiver_id, phone_number, category_id, price, status) values(?,?,?,?,?,?,?,?,?,?)";
        r.update(sql, p.getName(),p.getDetail(),p.getCover(),p.getPickup_address(),p.getProxy_address(),p.getReceiver_id(),p.getPhone_number(),p.getCategory_id()
        ,p.getPrice(),p.getStatus());
    }

    public void update(Product p)throws SQLException{
        QueryRunner r = new QueryRunner(DataSourceUtils.getDataSource());
        String sql = "update product set name=?, detail=?, cover=?, pickup_address=?, proxy_address=?, receiver_id=?, phone_number=?, category_id=?, price=?, status=? where id=?";
        r.update(sql, p.getName(),p.getDetail(),p.getCover(),p.getPickup_address(),p.getProxy_address(),p.getReceiver_id(),p.getPhone_number(),p.getCategory_id()
                ,p.getPrice(),p.getStatus(),p.getId());
    }

    public void delete(int id)throws SQLException{
        QueryRunner r = new QueryRunner(DataSourceUtils.getDataSource());
        String sql = "delete from product where id=?";
        r.update(sql, id);
    }
}

